package com.example.demo.common;

import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Component
@RequiredArgsConstructor
public class SqlCon {

    private final JdbcTemplate jdbcTemplate;

    private String sql = "";
    private final List<String> fieldName = new ArrayList<>();
    private final List<String> fieldValue = new ArrayList<>();

    public void setSql(String val) {
        sql = val;
    }

    public String getSql() {
        return sql;
    }

    public void setField(String val) {
        sql = "select " + val;
    }

    public void setTable(String val) {
        sql += " from " + val;
    }

    public void setWhere(String val) {
        sql += " where " + val;
    }

    public void setOrder(String val) {
        sql += " order by " + val;
    }

    public void setGroup(String val) {
        sql += " group by " + val;
    }

    public void setLimit(String val) {
        sql += " limit " + val;
    }

    public void addCollect(String field, String value) {
        fieldName.add(field);
        fieldValue.add(value);
    }

    public void clearCollect() {
        fieldName.clear();
        fieldValue.clear();
    }

    public int insertInfo(String tableName) {
        int count = fieldName.size();

        sql = "insert into " + tableName + "(";
        for (int i = 0; i < count; i++) {
            if (i == count - 1) {
                sql += fieldName.get(i) + ")";
            } else {
                sql += fieldName.get(i) + ", ";
            }
        }

        sql += " values(";
        for (int i = 0; i < count; i++) {
            if (i != count - 1) {
                sql += "?, ";
            } else {
                sql += "?)";
            }
        }

        Object[] obj = new Object[count];
        for (int i = 0; i < count; i++) {
            obj[i] = fieldValue.get(i);
        }
        clearCollect();

        return jdbcTemplate.update(sql, obj);
    }

    public int updateInfo(String tableName) {
        int count = fieldName.size();

        sql = "update " + tableName + " set ";
        for (int i = 0; i < count; i++) {
            if (i == count - 1) {
                sql += " where " + fieldName.get(i) + " = ?";
            } else if (i == count - 2) {
                sql += fieldName.get(i) + " = ? ";
            } else {
                sql += fieldName.get(i) + " = ?, ";
            }
        }

        Object[] obj = new Object[count];
        for (int i = 0; i < count; i++) {
            obj[i] = fieldValue.get(i);
        }
        clearCollect();

        return jdbcTemplate.update(sql, obj);
    }

    public int deleteInfo(String tableName) {
        int count = fieldName.size();

        sql = "delete from " + tableName + " where ";
        for (int i = 0; i < count; i++) {
            if (i == 0) {
                sql += fieldName.get(i) + " = ?";
            } else {
                sql += " and " + fieldName.get(i) + " = ?";
            }
        }

        Object[] obj = new Object[count];
        for (int i = 0; i < count; i++) {
            obj[i] = fieldValue.get(i);
        }
        clearCollect();

        return jdbcTemplate.update(sql, obj);
    }

    public Map<String, Object> getInfo(String field, String table, String where, Object[] obj) {
        setField(field);
        setTable(table);
        setWhere(where);

        List<Map<String, Object>> list = obj == null ? jdbcTemplate.queryForList(sql) : jdbcTemplate.queryForList(sql, obj);
        return list.size() != 1 ? null : list.get(0);
    }

    public List<Map<String, Object>> getList(String field, String table, String where, String order, Object[] obj) {
        setField(field);
        setTable(table);
        setWhere(where);
        setOrder(order);

        List<Map<String, Object>> list = obj == null ? jdbcTemplate.queryForList(sql) : jdbcTemplate.queryForList(sql, obj);
        return list.size() < 1 ? new ArrayList<>() : list;
    }

    public List<Map<String, Object>> getList(String field, String table, String where, String order, String group, Object[] obj) {
        setField(field);
        setTable(table);
        setWhere(where);
        setOrder(order);
        setGroup(group);

        List<Map<String, Object>> list = obj == null ? jdbcTemplate.queryForList(sql) : jdbcTemplate.queryForList(sql, obj);
        return list.size() < 1 ? new ArrayList<>() : list;
    }

    public List<Map<String, Object>> getList(String field, String table, String where, String order, String group, String limit, Object[] obj) {
        setField(field);
        setTable(table);
        setWhere(where);
        setOrder(order);
        setGroup(group);
        setLimit(limit);

        List<Map<String, Object>> list = obj == null ? jdbcTemplate.queryForList(sql) : jdbcTemplate.queryForList(sql, obj);
        return list.size() < 1 ? new ArrayList<>() : list;
    }
}
